SELECT * FROM [dbo].[cold_plan]

INSERT INTO [dbo].[cold_plan] ([start_time], [end_time]) VALUES ('2023-01-01 00:00:00', '2023-01-01 23:59:59');



delete cold_plan

SELECT count(*) FROM cold_plan

delete cold_clain_humiture

select * from cold_clain_humiture

-- 销售出库对比运输过账

SELECT * FROM kdl_sale_out_b where vendorbatch = '14240201' and vendorso = '1528SO24100831';

SELECT * FROM kdl_sale_out_qc where vendorbatch = '14240201' and vendorso = '1528SO24100831';



SELECT count(*) FROM kdl_mara


SELECT * FROM kdl_mara ORDER BY id asc


SELECT * FROM kdl_purchasein ORDER BY id desc

select top 1 store.mnecode from v_kdl_nc_so_order_detail sob left join ODS_NC_BD_DEFDOC store on sob.storcode=store.code where pk_order_b= '1001V110000000EI3BBG'


SELECT * FROM ods_nc_so_saleorder_b where csaleorderbid = '1001V110000000EI3BBG'

    1001V110000000DXP4OC

SELECT top 100 * FROM ods_nc_scm_batchcode where '1001V110000000EI3BBG'


SELECT * FROM v_kdl_custlist where customercode = '20384433';


SELECT * FROM ods_nc_bd_customer where code = '20384433'


select store.mnecode from v_kdl_nc_so_order_detail sob

                              left join ODS_NC_BD_DEFDOC store on sob.storcode=store.code
where pk_order_b in (select vendordnlineno FROM kdl_sale_out_qc where vendorso = '1528SO24060886')


SELECT * FROM
    SELECT vendorso FROM (select t1.vendorso,count(*) cnt FROM kdl_sale_out_qc t1 GROUP BY vendorso ) t2 where t2.cnt > 1 ORDER BY vendorso desc


select * FROM kdl_sale_out_qc where vendorso = '1528SO24070883'



select t1.vendorso,t3.mnecode FROM kdl_sale_out_qc  t1
                                       left join v_kdl_nc_so_order_detail t2 on t2.pk_order_b = t1.vendordnlineno
                                       left join ODS_NC_BD_DEFDOC t3 on t2.storcode=t3.code

where t3.mnecode <> '1001'




-- 主数据
-- 客户

SELECT * from v_kdl_custlist where customercode = '10011466'


SELECT cust148.def12,bd_customer.* from ods_nc_bd_customer  bd_customer left join ods_nc_shyy_customer_148 as cust148 on bd_customer.pk_customer =cust148.pk_customer and isnull(cust148.dr,0)=0  where code = '10011466'



SELECT top 3 * from nc_service_log where  submit_no = '111564_Z05-KDL' and id >= '151167' ORDER BY ID DESC



SELECT top 3 * from nc_service_log where  request_body like '1528CD24060061'


SELECT t.* FROM
    (SELECT submit_no from nc_service_log  GROUP BY submit_no) t where t.submit_no like '%-KDL'


SELECT top 2 * from nc_service_log where submit_time > '2024-06-22 09:30:00' and submit_no = '111564_Z05-KDL' ORDER BY ID DESC

SELECT sum(grquantity) FROM kdl_purchasein_b where pid = '58' and materialcode = '0119140290'


SELECT * FROM kdl_purchasein where vendorpo = '1528CD24060052'


-- 查日志   时间
-- 111564_Z01-KDL 正式
-- 111564_Z05-KDL 正式
-- 111385_Z04-KDL 销入
-- 111385_Z05-KDL 销售退货
-- 111564_Z04-KDL
-- 111385_Z01-KDL
-- 111564_RGR  正式拒收


SELECT * FROM nc_service_log where submit_time > '2024-06-21 18:05:04.317' and submit_time < '2024-06-21 20:05:04.317' and request_body like '%1528CD24060061%'




SELECT * FROM nc_service_log where create_time > '2024-10-30 18:05:04.317' and create_time < '2024-11-01 20:05:04.317' and request_body like '%1528CD24100083%'



SELECT * FROM nc_service_log where submit_no = '111564_RGR' and response_body like '%1528CD24060022%';

SELECT * from kdl_rgr where vendorpo = '1528CD24060043'

DELETE from kdl_rgr where id = 43

-- 111564_Z04-KDL 正式销出

SELECT * FROM nc_service_log where submit_no = '111564_Z04-KDL' and submit_time > '2025-04-14 07:43:24' and submit_time  < '2025-04-16 17:43:24' ORDER BY id asc



SELECT top 3 * FROM nc_service_log where id=158611 --submit_no = '111564_Z04-KDL'  order by id desc

                                     and submit_time > '2024-06-24 15:38:07' and submit_time  < '2024-06-24 21:08:07' ORDER BY id asc

SELECT * FROM nc_service_log where request_body like '%Z04%' and response_body  like '%1529SO24060654%'

select * from kdl_sale_out_qc where sync_time='2024-06-24 19:29:04'

select isnull(convert(varchar(20),max(sync_time),120),'2024-04-01 00:00:00') from kdl_sale_out_qc





SELECT top 10 * from kdl_purchasein_b ORDER BY id desc

SELECT * from kdl_purchasein_b where inspectionlot = '10006804855';

DELETE from kdl_purchasein_b where inspectionlot = '10006804855';

SELECT max(sync_time) from kdl_purchasein

SELECT * from kdl_purchasein where id = '486'

UPDATE kdl_purchasein set sync_time = '2024-11-18 12:41:05.000'


SELECT * from (SELECT count(*) cnt,sapbatch from kdl_purchasein_b GROUP BY sapbatch ) t where t.cnt >1


UPDATE kdl_purchasein_b  set boxflag = 'ZX' ,boxno = '',update_time = getdate() where sapbatch = '0012014694';

SELECT inspectionlot,vendorso, * FROM kdl_sale_out_qc where inspectionlot = '100101816226';

select inspectionlot,status,inspectionresult,* FROM kdl_sale_out_qc where vendorso = '1528SO24062621' and inspectionlot not in  ('100102144357','100102144358','100102144359','100102144360','100102144361','100102144362','100102144363','100102144364','100102144365')


update kdl_sale_out_qc set status = 'Y' where  vendorso = '1528SO24070464' and inspectionlot in  ('100102232405')






    3664
3665
3666
3667
3668

delete kdl_sale_out_qc where  vendorso = '1528SO24070227' and inspectionlot not in  ('100102144357','100102144358','100102144359','100102144360','100102144361','100102144362','100102144363','100102144364','100102144365')

--  正式移库 111564_GM

SELECT * FROM nc_service_log where submit_no = '111564_GM' and response_body like '%0000000088786926%' and create_time > '2024-07-14';
SELECT  * FROM kdl_gm WHERE docno = '0000000088883305';
SELECT  * FROM kdl_gm WHERE docno = '0000000088974836'
SELECT  * FROM kdl_gm order by id desc WHERE principalorderno =
update  kdl_gm set principalorderno = 'FK20240300*' WHERE docno = '0000000088821335'

update  kdl_gm set status = '已回写' where docno = '0000000088649304'
-- 销退

SELECT * FROM kdl_z05kdl where vendorso = '1528SO24081127';

SELECT inspectionlot FROM kdl_z05kdl_b where pid = (SELECT id FROM kdl_z05kdl where vendorso = '1528SO24081127');

SELECT * FROM kdl_inspectionresult_status where inspectionlot in (SELECT inspectionlot FROM kdl_z05kdl_b where pid =  (SELECT id FROM kdl_z05kdl where vendorso = '1528SO24081127'));


SELECT * FROM kdl_inspectionresult_status where inspectionlot = '50001338183';

INSERT INTO kdl_inspectionresult_status ([inspectionlot], [cwarehouseid], [status], [create_user],  [update_user], [update_time], [mark]) VALUES ('50001340812', '1010', '已回写', 1, 0, NULL, 1);


DELETE kdl_inspectionresult_status where inspectionlot = '50001338183';


DELETE from kdl_rgr where id = '44'

SELECT * from kdl_z05kdl_b where inspectionlot ='50001328758'





SELECT top 10 * FROM ods_nc_scm_batchcode ORDER BY ts desc





-- qff

SELECT * from kdl_qff_feedback order by id desc

SELECT * from kdl_gm where boxflag = ''


SELECT * from kdl_gm where fromlocation = tolocation and boxflagto = ''

update kdl_gm set status = '已回写' where fromlocation = tolocation and boxflagto is null


update kdl_gm set status = '已回写' where id = '85'


-- 采购入库

SELECT * FROM kdl_purchasein t1 RIGHT JOIN  kdl_purchasein_b t2 on t1.id = t2.pid where t1.vendorpo = '1528CD24060075';




--  销退


INSERT INTO [dbo].[kdl_inspectionresult_status] ([inspectionlot], [cwarehouseid], [status], [create_user], [create_time], [update_user], [update_time], [mark]) VALUES ( '50001354340', '1012', '已回写', 1, getdate(), 0, NULL, 1);



SELECT * from kdl_inspectionresult_status where inspectionlot = '50001332292'


update kdl_inspectionresult_status set cwarehouseid = '1012' where inspectionlot = '50001329216'

-- 采购

SELECT * from kdl_purchasein_b where  vlotno = '2320403'



-- 批号

SELECT top 10 * from ods_nc_scm_batchcode order by ts desc



SELECT * from ods_nc_scm_batchcode where CMATERIALOID like '1001V110000000E56IBR%' or PK_BATCHCODE like '1001V110000000E56IBR%'
                                      or CMATERIALVID like '1001V110000000E56IBR%'

select isnull(batch.vbatchcode,'')+','+isnull(batch.vdef12,'')+','+isnull(replace(batch.vdef16,'~',''),'') vbatchcode from ods_nc_so_saleorder_b so_b left join ods_nc_scm_batchcode batch on so_b.pk_batchcode=batch.pk_batchcode where so_b.csaleorderbid = '1001V110000000E698VX'



SELECT pk_batchcode from ods_nc_so_saleorder_b where csaleorderbid = '1001V110000000E56IBR'


SELECT * from ods_nc_so_saleorder_b where csaleorderbid = '1001V110000000E56IBR'

    csaleorderbid
1528SO24070789
SELECT top 10 * from ods_nc_so_saleorder_b ORDER BY ts desc


SELECT * from ods_nc_so_saleorder where vbillcode = '1528SO24070789'

    csaleorderid
csaleorderbid




SELECT * from v_kdl_nc_so_order_detail where PK_ORDER_B = '1001V110000000E56IBR'


SELECT * from v_kdl_nc_so_order where PK_ORDER = '1001V110000000E56IBP'

SELECT * FROM v_kdl_custlist where customercode = '20075923'




SELECT * FROM kdl_gm where docno in ('0000000088840799',
                                     '0000000088840803',
                                     '0000000088844187',
                                     '0000000088845082'
    )

update kdl_gm set tolocation = '1001' where docno in ('0000000088840799',
                                                      '0000000088840803',
                                                      '0000000088844187',
                                                      '0000000088845082'
    )

update kdl_gm set inoutstep = 1 where docno in ('0000000088840799'
    )
select * from v_kdl_gm where orderno='4917907946' and companycode='1528' and fromlocation='1001' and tolocation= '' and isnull(status,'未回写')<>'已回写'

update kdl_gm set inoutstep = (SELECT inoutstep FROM kdl_gm where docno = '0000000088840799') + 1 where docno in '0000000088840799'






select t1.inspectiondate1,t2.grdate from kdl_purchasein_b  t1 LEFT JOIN  kdl_purchasein t2 on t1.pid = t2.id






select distinct po_invoice.pk_invoice id, po_invoice.pk_org corp, po_invoice.vbillcode code, po_invoice.vtrantypecode transtype, po_invoice.ctrantypeid transtypepk, po_invoice_b.csourceid sourceID
from ods_nc_po_invoice po_invoice, ods_nc_po_invoice_b po_invoice_b where po_invoice.pk_invoice = po_invoice_b.pk_invoice and po_invoice_b.csourcetypecode in ( '45', '45' ) and po_invoice_b.csourceid in ( '10012110000000DXG5JK', '10012110000000DUKF2U', '10012110000000DXFBI9' ) and po_invoice_b.csourceid != '~' and po_invoice.dr = 0 and po_invoice_b.dr = 0

csourceid

SELECT top 10 b.CFIRSTBID,b.* FROM ODS_NC_PO_ORDER_B b

SELECT * FROM kdl_z05kdl_b kdlb LEFT JOIN ODS_NC_PO_ORDER_B ncb on ncb.pk_order_b = kdlb.sn

SELECT * FROM ODS_NC_PO_ORDER

SELECT * FROM ods_nc_po_order_b poob LEFT JOIN ods_nc_po_invoice poi on poob.cfirstid =
                                                                        po_invoice_b.cfirstid =po_order_b.pk_order_b









SELECT * FROM v_gsp_shiprecords where MATERIALTYPE like '%第二%' order by dispatch_date desc




SELECT * from kdl_purchasein_b where vendorbatch = '72240532'

UPDATE  kdl_purchasein_b set vendorbatch = '72240532' ,boxno = '72240531&72240532',wodeliverynote = '此单kdl修正批号原72740532到72240532' where vendorbatch = '72740532'






SELECT * FROM kdl_sale_out_qc where vendorso in ('1528SO24081983','1529SO24080916')

UPDATE kdl_sale_out_qc set status = 'Y' where vendorso in ('1528SO24081983','1529SO24080916')


-- 销售出库 检查kdl出库到NC仓库是否被异常改变


SELECT  tt.* from
    (
        SELECT  ABS(b.NNUM) as ccc, b.CGENERALHID,b.csourcebillbid,h.vbillcode,m.warehouse_code,h.cwarehouseid,z05b.cwarehouseid as kdl_warehouse_code,z05b.vendorso,z05b.vlotno,z05b.pk_batchcode,z05b.nnum,z05b.inspectiondate1
        FROM ODS_NC_IC_SALEOUT_B b
                 inner JOIN  ODS_NC_IC_SALEOUT_H h on b.CGENERALHID = h.CGENERALHID
                 inner JOIN kdl_warehouse_mapper m on m.nc_id = h.cwarehouseid
                 inner JOIN v_kdl_salereturn_inspectionresult  z05b on z05b.vendorsolineno = b.csourcebillbid
            and ABS(b.NNUM) = z05b.nnum
        WHERE b.dr = '0' and z05b.inspectiondate1 > '2024-08-01' and z05b.cwarehouseid <> '1012'
    ) tt
where
        tt.warehouse_code <> tt.kdl_warehouse_code




--



--and b.CGENERALHID = '10012110000000EE8IRC'
--and b.csourcebillbid = '1001V110000000EBFO6W'
--and h.vbillcode = '101050001336985'


SELECT * FROM kdl_purchasein ORDER BY id desc

    LEFT(ENDOFMONTH(DATEINMONTH($sdbilldate,0),-2),10)



SELECT convert(numeric(11,4),sum(t.nnum/t.umrez)) as total from v_lo_purchase t where 1=1
                                                                                  AND grdate > '2024-06-31' AND grdate <= '2024-07-31'
                                                                                  AND boxflag in ('ZX')








delete kdl_sale_out_qc where inspectionlot in ('100103567235','100103567175','100103731100','100103730837','100103730874','100103731153','100103731254','100103731252','100103731253','100103730892','100103730894','100103730893','100103730888','100103731141','100103731186','100103730850','100103731138','100103731187')



SELECT cahdnno,cwarehouseid FROM v_gsp_outcheck GROUP BY cahdnno,cwarehouseid


SELECT orderno,* from kdl_gm order by id desc where docno = '10084918194376'

SELECT orderno,* from kdl_gm where vendorbatch = '67240201' order by id desc


SELECT t3.* FROM (
                     SELECT t2.vendorso ,count(t2.vendorso) as OccurrenceCount FROM

                         (

                             SELECT  t.vendorso ,t.mnecode
                             FROM (SELECT * from kdl_sale_out_qc out_qc
                                                     left join(select distinct sob.pk_order_b,store.mnecode from  v_kdl_nc_so_order_detail sob
                                                                                                                      left join ODS_NC_BD_DEFDOC store on sob.storcode=store.code) temp on temp.pk_order_b=out_qc.vendordnlineno)
                                      t
                             GROUP BY t.vendorso , t.mnecode
                         ) t2
                     GROUP BY t2.vendorso
                 ) t3
where t3.OccurrenceCount > 1
ORDER BY t3.vendorso desc


select * from kdl_sale_out_qc where vendorso in ('1528SO24111082','1528SO24111054')

delete kdl_sale_out_qc where vendorso in ('1528SO24111489','1528SO24111486','1528SO24111222')

 -- 删除销售出库




update kdl_sale_out_qc set status = 'Y'  where vendorso in ('1528SO24090332','1528SO24090326')



SELECT  ManufacturerName,RegistrationFilingapplicantname,Commissionedmanufacturername,cstoragecon as 储藏条件 ,jylx as 基药类型 ,* from kdl_material where RegistrationFilingapplicantname <> ManufacturerName



select top 100 *,mnecode FROM kdl_sale_out_qc out_qc

                                  left join(select distinct sob.pk_order_b,store.mnecode from  v_kdl_nc_so_order_detail sob
                                                                                                   left join ODS_NC_BD_DEFDOC store on sob.storcode=store.code) temp on temp.pk_order_b=out_qc.vendordnlineno

